﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class ReportDolgObject
    {
        public const string sql = @"
SELECT 'Долги:' as Описание, '' as Партнер,'' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryPartners.Name as Партнер, DictionaryProject.Name as Проект, convert(nchar(20),TotalDolg.Summa) as Сумма
FROM (
   select Partner, Project, sum(Summa) as Summa
   from Transactions 
   where Type = 1 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_dox_dolg%')
   group by Partner, Project) as TotalDolg 
LEFT JOIN DictionaryProject on TotalDolg.Project = DictionaryProject.ID
LEFT JOIN DictionaryPartners on TotalDolg.Partner = DictionaryPartners.ID
UNION ALL
SELECT 'Возврат долгов:'  as Описание, '' as Партнер,'' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryPartners.Name as Партнер, DictionaryProject.Name as Проект, convert(nchar(20),TotalOtdano.Summa) as Сумма
FROM (
   select Partner, Project, sum(Summa) as Summa
   from Transactions 
   where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_racx_dolg%')
   group by Partner, Project) as TotalOtdano 
LEFT JOIN DictionaryProject on TotalOtdano.Project = DictionaryProject.ID
LEFT JOIN DictionaryPartners on TotalOtdano.Partner = DictionaryPartners.ID
UNION ALL
SELECT 'Необходимо вернуть:' as Описание,'' as Партнер, '' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryPartners.Name as Партнер, DictionaryProject.Name as Проект, convert(nchar(20),sum(raznitsa2.Summa)) as Сумма
FROM (
     select Partner, Project, sum(Summa) as Summa
     from Transactions 
     where Type = 1 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_dox_dolg%')
     group by Partner, Project
     union all
     select Partner, Project, -sum(Summa) as Summa
     from Transactions 
     where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_racx_dolg%')
     group by Partner, Project
     ) as raznitsa2
LEFT JOIN DictionaryProject on raznitsa2.Project = DictionaryProject.ID
LEFT JOIN DictionaryPartners on raznitsa2.Partner = DictionaryPartners.ID
GROUP BY DictionaryPartners.Name , DictionaryProject.Name
UNION ALL
select '' as Описание, '' as Партнер, '' as Проект, '' as Сумма
UNION ALL
select 'Всего долгов:' as Описание, 'Все партнеры' as Партнер, 'Все проекты' as Проект, convert(nchar(20),sum(Summa)) as Сумма from Transactions where Type = 1 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_dox_dolg%')
UNION ALL
select 'Всего отдано долгов:' as Название, 'Все партнеры' as Партнер, 'Все проекты' as Проект, convert(nchar(20),sum(Summa)) as Значение from Transactions where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_racx_dolg%')
UNION ALL
SELECT 'Необходимо отдать:' as Название, 'Все партнеры' as Партнер, 'Все проекты' as Проект, convert(nchar(20),sum(raznitsa1.Summa)) as Значение FROM
(select sum(Summa) as Summa from Transactions where Type = 1 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_dox_dolg%')
union all
select -sum(Summa) as Summa from Transactions where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_cat_racx_dolg%')) as raznitsa1
";

        public string Описание { get; set; }
        public string Партнер { get; set; }
        public string Проект { get; set; }
        public string Сумма { get; set; }
    }
}
